package datamodule;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import util.FString;
import conexao.Connect;

public class HistoricoDAO {


	private DBuserVO userVO;

	public HistoricoDAO( DBuserVO userVO){
		this.userVO = userVO;
	}
	//Inserir Historico
	public int inserir(HistoricoVO VOhis) throws ClassNotFoundException, SQLException {
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmtInsert = con.prepareStatement(
				"INSERT INTO historico(his_Cod_His, his_Descricao) " +
				" VALUES(?,?) " );
		pstmtInsert.setString(1, VOhis.getHis_Cod_His());			
		pstmtInsert.setString(2, VOhis.getHis_Descricao());
		
		int reg = pstmtInsert.executeUpdate();	
		con.close();
		return reg;
	}

	//Alterar Historicos
	public int alterar(HistoricoVO VOhis) throws ClassNotFoundException, SQLException {
		Connection con = Connect.getConnection(userVO);
		String virgula = "";
		StringBuffer sql = new StringBuffer(" UPDATE historico SET ");
		if (!VOhis.getHis_Cod_His().equals(FString.ignorar)) {
			sql.append(virgula+" his_Cod_His = '"+VOhis.getHis_Cod_His()+"' ");
			virgula = ",";
		}
		if (!VOhis.getHis_Descricao().equals(FString.ignorar)) {
			sql.append(virgula+" his_Descricao = '"+VOhis.getHis_Descricao()+"' ");
			virgula = ",";
		}

		sql.append(" WHERE his_Cod_His = '"+VOhis.getHis_Cod_His()+"' ");
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0, sql.length()));
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}	
	
	// Deletar Historico
	public int deletar(HistoricoVO VOhis) throws ClassNotFoundException, SQLException {		
		Connection con = Connect.getConnection(userVO);
		PreparedStatement pstmt = con.prepareStatement(
		"DELETE FROM historico WHERE his_Cod_His = ?");
		pstmt.setString(1, VOhis.getHis_Cod_His());
		int registrosAfetados = pstmt.executeUpdate();
		con.close();
		return registrosAfetados;
	}

	//Pesquisar Historico
	public HistoricoVO pesquisar(HistoricoVO VOhis) throws ClassNotFoundException, SQLException {				
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer("SELECT * FROM historico Where his_Cod_His <> '' ");
		
		if (!VOhis.getHis_Cod_His().equals(FString.ignorar)) {
			sql.append(" AND UPPER(his_Cod_His) = '"+VOhis.getHis_Cod_His()+"' ");			
		}		
		if (!VOhis.getHis_Descricao().equals(FString.ignorar)) {
			sql.append(" AND UPPER(his_Descricao) = '"+VOhis.getHis_Descricao()+"' ");			
		}
	
		//System.out.println(sql);
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {

			VOhis = new HistoricoVO();
			VOhis.setHis_Cod_His( rs.getString("his_Cod_His"));
			VOhis.setHis_Descricao ( rs.getString("his_Descricao"));
			
		}else {
			VOhis = null;
		}
		pstmt.close();
		rs.close();
		con.close();
		return VOhis;
	}

	public ArrayList<HistoricoVO> geral(HistoricoVO VOhis) throws SQLException, ClassNotFoundException {		
		ArrayList<HistoricoVO> lista = null;
		Connection con = Connect.getConnection(userVO);
		StringBuffer sql = new StringBuffer("SELECT * FROM historico  Where his_Cod_His <> '' ");
		if (!VOhis.getHis_Cod_His().equals(FString.ignorar)) {
			sql.append(" AND UPPER(his_Cod_His) LIKE UPPER('"+VOhis.getHis_Cod_His()+"%"+"')");
		}
		if (!VOhis.getHis_Descricao().equals(FString.ignorar)) {
			sql.append(" AND UPPER(his_Descricao) LIKE UPPER('"+VOhis.getHis_Descricao()+"%"+"')");
		}
		if (!VOhis.getOrdem().equals(FString.ignorar)) {
			sql.append(" ORDER BY "+VOhis.getOrdem());
		}
		if (!VOhis.getLimit().equals(FString.ignorar)) {
			sql.append(" LIMIT "+VOhis.getLimit());
		}
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));		
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			lista = new ArrayList<HistoricoVO>();
			do {
				HistoricoVO his = new HistoricoVO();
				his.setHis_Cod_His( rs.getString("his_Cod_His"));
				his.setHis_Descricao ( rs.getString("his_Descricao"));
			
				lista.add(his);
			}
			while (rs.next());			
		}
		pstmt.close();
		rs.close();
		con.close();
		return lista;
	}	
	public ResultSet listagem(HistoricoVO VOhis, Connection con) throws SQLException, ClassNotFoundException {		
		StringBuffer sql = new StringBuffer("SELECT "+
		" historico.`his_Descricao` AS historico_his_Descricao, "+
		" historico.`his_Cod_His` AS historico_his_Cod_His "+
		" FROM `historico` historico "+
		" WHERE historico.`his_Descricao` <> '' ");		

		if (!VOhis.getHis_Cod_His().equals(FString.ignorar) && !VOhis.getHis_Cod_His().isEmpty()) {
			sql.append(" AND UPPER(historico.`his_Cod_His`) = '"+VOhis.getHis_Cod_His()+"'");			
		}		
		if (!VOhis.getHis_Descricao().equals(FString.ignorar) && !VOhis.getHis_Descricao().isEmpty()) {
			sql.append(" AND UPPER(historico.`his_Descricao`) like '"+VOhis.getHis_Descricao()+"%"+"'");			
		}
		
		if (!VOhis.getOrdem().equals(FString.ignorar) && !VOhis.getOrdem().isEmpty()) {
			sql.append(" ORDER BY "+VOhis.getOrdem());
		}
		if (!VOhis.getLimit().equals(FString.ignorar)) {
			sql.append(" LIMIT "+VOhis.getLimit());
		}
		PreparedStatement pstmt = con.prepareStatement(sql.substring(0,sql.length()));
		System.out.println(pstmt);
		ResultSet rs = pstmt.executeQuery();
		return rs;
	}	
			
}